# http://www.markcarter.me.uk/computing/python/ado.html

import os
import re
import sys
import string
import win32com.client

def main(ifile):
    ''' > export metadata and data of MS Access DB
    '''
    FILE = ifile
    CWD = re.sub(r'\\', '/', os.getcwd()) + '/'
    PATH = CWD + FILE
    ExportData = True


    resf = open('%s_metadata.txt'%FILE, 'w')

    DTYPES = {3:'AutoNumber', 202:'Text', 5:'Number(Double)', 7:'Date/Time',
              2:'Number(Integer)', 4:'Number(Single)',
              11:'Boolean'}
    
    # 203 : 'Memo', 205: 'OLE Object', 6:'Currency', 

    # CREATE CONNECTION
    conn = win32com.client.Dispatch(r'ADODB.Connection')
    #DSN = 'PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=C:/AFT/Northwind.mdb;'
    DSN = 'PROVIDER=Microsoft.Jet.OLEDB.4.0;DATA SOURCE=%s;'%PATH
    conn.Open(DSN)

    # TABLES
    oCat = win32com.client.Dispatch(r'ADOX.Catalog')
    oCat.ActiveConnection = conn
    oTab = oCat.Tables
    for o in oTab:
        if o.Type == 'TABLE':

            #print o.Name,
            resf.write(o.Name)

            # CREATE FOR EACH TABLE DIFFERENT RECORSET
            rs = win32com.client.Dispatch(r'ADODB.Recordset')
            rs_name = o.Name
            rs.Open('[' + rs_name + ']', conn, 1, 3)

            rest = open('%s_%s.txt'%(FILE, o.Name), 'w')

            # COUNT ROWs
            sql_statement = "SELECT * FROM [%s]"%(o.Name)
            conn.Execute(sql_statement)
            #print rs.RecordCount # no parenthesis here either
            resf.write(' :: %s\n'%rs.RecordCount)

            # SHOW HEADER STRUCTURE
            for x in range(rs.Fields.Count):
                resf.write('\'NAME\':\'%s\''%rs.Fields.Item(x).Name)
                #dt = ACCESS_DATA_TYPES[rs.Fields.Item(x).Type]
                colDataType = rs.Fields.Item(x).Type
                resf.write(', \'TYPE\':\'%s\''%colDataType)
                resf.write(', \'SIZE\':%s\n'%rs.Fields.Item(x).DefinedSize)

            for x in range(rs.Fields.Count):
                resf.write('~%s'%rs.Fields.Item(x).Name)

            if ExportData:
                # EXPORT DATA
                rs.MoveFirst()
                count = 0
                while 1:
                    # JUST FIRST ...
                    #if count > 2:
                    # WHOLE TABLE
                    if rs.EOF:
                        break
                    else:
                        for x in range(rs.Fields.Count):
                            if rs.Fields.Item(x).Type in DTYPES.keys():
                                v = rs.Fields[x].Value
                                if DTYPES[rs.Fields.Item(x).Type] == 'Text':
                                    try:
                                        v = v.encode("utf-8")
                                    except:
                                        pass
                                    v = re.sub('\x0D\x0A', '', str(v))
                                #print o.Name, rs.Fields[x].Name, v
                                rest.write('%s~'%v)
                            else:
                                rest.write('%s~'%'<skipped :: %s>'%rs.Fields.Item(x).Type)
                        rest.write('\n')
                    count = count + 1
                    rs.MoveNext()

            resf.write('\n::\n')
            rs.Close()
            rs = None
            rest.close()
    conn.Close()
    conn = None

if __name__ == '__main__':

    if len(sys.argv) == 2:
        main(ifile=sys.argv[1])
    else:
        print main.__doc__

    #main(ifile="Northwind.mdb")
    
